﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DataHelper;
using Utils; //使用HTMLEncode 
//产品主表，明细表插入
namespace DAL
{
    public class BuiltSQL : IBuiltSQL
    {
        public DataTable GetOneInfo(string tableName, string[] primaryKey)
        {
            string strMainSQL = "Select * from " + tableName + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "S")
                strMainSQL += "'" + primaryKey[1].Trim() + "'";
            else
                strMainSQL += Int32.Parse(primaryKey[1].Trim());
            LogHelper.WriteLog(strMainSQL);
            return SQLHelper.ExecuteSqlGetDrToDt(strMainSQL);
        }
        public DataTable GetList(string tableName, string filter)
        {
            string strMainSQL = "Select * from " + tableName + " WHERE " + filter;
            return SQLHelper.ExecuteSqlGetDrToDt(strMainSQL);
        }
        public bool ExecSQL(string strSQL)
        {
            LogHelper.WriteLog(strSQL);
            return SQLHelper.ExecuteSqlWithTransaction(strSQL);

        }
        public DataSet ExecSQL2(string strSQL)
        {
            LogHelper.WriteLog(strSQL);
            return SQLHelper.ExecuteSqlGetDataSet(strSQL);

        }


        /// <summary>
        /// 对字段进行格式化
        /// </summary>
        /// <param name="dt">该表所有字段的类型、长度等</param>
        /// <param name="key">该字段</param>
        /// <param name="sdList">传入的数据列表</param>
        /// <returns></returns>
        private string GetValue(DataTable dt, string key, SortedDictionary<string, string> sdList)
        {
            string fType = "";
            int fLength = 0;
            for (int t = 0; t < dt.Rows.Count; t++)
            {
                if (dt.Rows[t]["col_name"].ToString().Trim() == key) //注意：字段不包括 [],所以定义表的时候，不要使用关键字
                {
                    fType = dt.Rows[t]["col_type"].ToString();
                    fLength = Int32.Parse(dt.Rows[t]["col_len"].ToString());
                }
            }
            string theValues = sdList[key];
            if (theValues == "null" || theValues=="") return "null"; //20110316
            string returnValue = "";//返回的字串
            switch (fType)
            {
                case "int":
                case "float":
                case "tinyint":
                case "decimal":
                case "bit":
                case "bigint":
                case "smallint":
                case "money":
                case "smallmoney":
                case "real":
                case "numeric":

                    if (string.IsNullOrWhiteSpace(theValues))
                    {
                        theValues = "0";
                    }
                    else
                    {
                        try
                        {
                            double tmp = double.Parse(theValues);
                        }
                        catch (Exception e)
                        {
                            LogHelper.WriteLog("输入的数据应该为数据: " + theValues, e);
                            throw new Exception(e.Message);
                        }
                    }
                    returnValue = theValues;

                    break;
                case "datetime":
                case "smalldatetime":
                case "timestamp":
                    returnValue = "'" + theValues + "'";
                    break;
                default://string等 要不要判断非空
                    if (theValues.Length > fLength)
                        returnValue = "'" + theValues.Substring(0, fLength) + "'";
                    else
                        returnValue += "'" + theValues + "'";
                    break;
            }

            return returnValue;
        }
        #region  向单独一个表中插入数据
        /// <summary>
        /// 向单独一个表中插入数据
        /// </summary>
        /// <param name="mainTable">表名</param>
        /// <param name="sdList">往表中将要插入的数据 [key,value]</param>  
        /// <returns>成功与否的标志</returns>
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList)
        {
            /*
            //获取表的结构,是什么数据类型，长度等
            DataTable dt = TableInfo.GetTableStruct(tableName);
            int itemCount = 0;//元素个数

            string field = "";//表的字段
            string fieldValue = "";//表的字段的值


            foreach (string key in sdList.Keys)
            {
                itemCount++;
                string thisValue = GetValue(dt, key, sdList);
                if (string.IsNullOrEmpty(thisValue)) continue;
                field += key;
                fieldValue += thisValue;
                // fieldValue += HttpUtility.HtmlEncode(thisValue);

                if (itemCount < sdList.Count)
                {
                    field += ",";
                    fieldValue += ",";
                }

            }
            string strMainSQL = "INSERT INTO " + tableName + " (" + field + ") VALUES (" + fieldValue + ")";
            */
           string strMainSQL=GetInsertSql(tableName, sdList);

            bool flag = false;
            try
            {

                flag = SQLHelper.ExecuteSql(strMainSQL);
                LogHelper.WriteLog(strMainSQL);
            }
            catch (Exception e)
            {
                LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
                // throw new Exception(e.Message);
            }
            return flag;
        }
        /// <summary>
        /// 向表中插入一条数据， 同时执行其它一条或多条SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="sdList">需要插入的数据</param>
        /// <param name="arrayList">同时需要执行的SQL语句</param>
        /// <returns></returns>
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
        {
            string strMainSQL = GetInsertSql(tableName, sdList);
            for (int i = 0; i < arrayList.Count; i++)
            {
                string sql = arrayList[i].ToString().Trim();
                strMainSQL += sql;
                if (!sql.EndsWith(";"))
                    strMainSQL += ";"; 
            }

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSqlWithTransaction(strMainSQL);
                // LogHelper.WriteLog(strMainSQL);
            }
            catch (Exception e)
            {
                LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;
        
        }

        public string GetInsertSql(string tableName, SortedDictionary<string, string> sdList)
        {
            //获取表的结构,是什么数据类型，长度等
            DataTable dt = TableInfo.GetTableStruct(tableName);
            int itemCount = 0;//元素个数

            string field = "";//表的字段
            string fieldValue = "";//表的字段的值


            foreach (string key in sdList.Keys)
            {
                itemCount++;
                string thisValue = GetValue(dt, key, sdList);
                if (string.IsNullOrEmpty(thisValue)) continue;
                field += key;
                fieldValue += thisValue;
                // fieldValue += HttpUtility.HtmlEncode(thisValue);

                if (itemCount < sdList.Count)
                {
                    field += ",";
                    fieldValue += ",";
                }

            }
            string strMainSQL = "INSERT INTO " + tableName + " (" + field + ") VALUES (" + fieldValue + ");";

            return strMainSQL;
        }
        #endregion
      
       

        #region 删除一条记录
        /// <summary>
        /// 从单个表中删除数据
        /// </summary>
        /// <param name="mainTable">表名</param>
        /// <param name="primaryKey">主键 new string[] { "Id",id, "N" };</param>
        /// <returns></returns>
        public string DeleteTable(string tableName, string[] primaryKey)
        {
            string result = "";
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "S")
                strMainSQL += "'" + primaryKey[1].Trim() + "'";
            else
                strMainSQL += primaryKey[1].Trim();

            try
            {
                //LogHelper.WriteLog(strMainSQL);
                SQLHelper.ExecuteSql(strMainSQL);
                result = "删除成功";

            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
        /// <summary>
        /// 删除 被自己定义的业务逻辑层调用
        /// 带其它SQL语句同时操作
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKey">主键 new string[] { "Id",id, "N" };</param>
        /// <param name="arrayList">其它完整SQL语句</param>
        /// <returns></returns>
       public string DeleteTable(string tableName, string[] primaryKey, ArrayList arrayList)
        {
            string result = "";
           StringBuilder  strMainSQL = new StringBuilder("Delete " + tableName + " WHERE " + primaryKey[0] + "=");
            if (primaryKey[2] == "S")
                strMainSQL.Append("'" + primaryKey[1].Trim() + "'");
            else
                strMainSQL.Append( Int32.Parse(primaryKey[1].Trim()));
            strMainSQL.Append("; ");
            for (int i = 0; i < arrayList.Count; i++)
            {
                string sql = arrayList[i].ToString().Trim();
                strMainSQL.Append(sql);
                if (!sql.EndsWith(";"))
                    strMainSQL.Append("; ");
            }
            try
            {
                //LogHelper.WriteLog(strMainSQL);
                if(SQLHelper.ExecuteSqlWithTransaction(strMainSQL.ToString()))
                   result = "删除成功";
                else
                    result = "删除失败";

            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;

        }
       #endregion


       #region  更新一个表中数据
       /// <summary>
       /// 更新一个表中插入数据
       /// </summary>
       /// <param name="mainTable">表名</param>
       /// <param name="mainList">将要更新的数据 [key,value,type]</param>
       /// <param name="primaryKey">主键 [主键][值][类型]</param>
       /// S: 字符串 N：整数 D：decimal数字 F：浮点数 A：日期
       /// <returns></returns>

       public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList)
        {
            /*
             DataTable dt = TableInfo.GetTableStruct(tableName);
             int len = sdList.Count;

             string condition = "";
             string priFiled = "";
             for (int t = 0; t < dt.Rows.Count; t++)
             {
                 if (dt.Rows[t]["is_key"].ToString().Trim() == "1")
                 {
                     priFiled = dt.Rows[t]["col_name"].ToString().Trim();
                     break;
                 }
             }

             StringBuilder stringBuilder = new StringBuilder();
             string keyAndValue = "";
             foreach (string key in sdList.Keys)
             {
                 string value = GetValue(dt, key, sdList);
                 if (priFiled == key)
                 {
                     condition = key + "=" + value;
                 }
                 else
                 {
                     if (value == "null")
                         stringBuilder.Append(key + "=null,");
                     else
                         stringBuilder.Append(key + "=" + value + ",");
                     //keyAndValue += key;
                     //keyAndValue += "=";
                     //keyAndValue += value;
                     //keyAndValue += ",";
                 }
             }
             keyAndValue = stringBuilder.ToString().TrimEnd();
             if (keyAndValue.EndsWith(",")) keyAndValue = keyAndValue.Substring(0, keyAndValue.Length - 1);
             string strMainSQL = "UPDATE " + tableName + " SET " + keyAndValue + " WHERE  " + condition;
            */
            string strMainSQL = GetUpdateSql( tableName, sdList);// 
            bool flag = false;
            try
            {
              // LogHelper.WriteLog(strMainSQL);
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("更新数据失败:" + strMainSQL, ex);
            }
            return flag;
        }
       public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
       {
           string strMainSQL = GetUpdateSql(tableName, sdList);
           for (int i = 0; i < arrayList.Count; i++)
           {
               string sql = arrayList[i].ToString().Trim();
               strMainSQL += sql;
               if (!sql.EndsWith(";"))
                   strMainSQL += ";";
           }

           bool flag = false;
           try
           {
               flag = SQLHelper.ExecuteSqlWithTransaction(strMainSQL);
               // LogHelper.WriteLog(strMainSQL);
           }
           catch (Exception e)
           {
               LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
           }
           return flag;

       }
       public string GetUpdateSql(string tableName, SortedDictionary<string, string> sdList)
       {
           DataTable dt = TableInfo.GetTableStruct(tableName);
           int len = sdList.Count;

           string condition = "";
           string priFiled = "";
           for (int t = 0; t < dt.Rows.Count; t++)
           {
               if (dt.Rows[t]["is_key"].ToString().Trim() == "1")
               {
                   priFiled = dt.Rows[t]["col_name"].ToString().Trim();
                   break;
               }
           }

           StringBuilder stringBuilder = new StringBuilder();
           string keyAndValue = "";
           foreach (string key in sdList.Keys)
           {
               string value = GetValue(dt, key, sdList);
               if (priFiled == key)
               {
                   condition = key + "=" + value;
               }
               else
               {
                   if (value == "null")
                       stringBuilder.Append(key + "=null,");
                   else
                       stringBuilder.Append(key + "=" + value + ",");
                   //keyAndValue += key;
                   //keyAndValue += "=";
                   //keyAndValue += value;
                   //keyAndValue += ",";
               }
           }
           keyAndValue = stringBuilder.ToString().TrimEnd();
           if (keyAndValue.EndsWith(",")) keyAndValue = keyAndValue.Substring(0, keyAndValue.Length - 1);
           return "UPDATE " + tableName + " SET " + keyAndValue + " WHERE  " + condition+";";
       }

        #endregion

        public string InsertMultiTable(List<String[,]> detailList)
        {
            string result = "";
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                string subKey = "";
                string subValue = "";
                string[,] item = detailList[j];


                for (int i = 1; i < item.GetLength(0); i++)
                {

                    subKey += item[i, 0].ToString();//键
                    ////N是整数 D是decimal
                    if (item[i, 2].ToString() == "N")//类型
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "D")
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "A")//date 的第二个字母
                        subValue += "'" + item[i, 1].ToString() + "'";//值
                    else
                        subValue += "'" + item[i, 1].Trim() + "'";
                    if (i < (item.GetLength(0) - 1))
                    {
                        subKey += ",";
                        subValue += ",";
                    }
                }
                strDetailSQL += " INSERT INTO " + item[0, 1].ToString() + " (" + subKey + ") VALUES (" + subValue + ")";
                if (j < count - 1)
                    strDetailSQL += ";";
            }



            try
            {
                SQLHelper.ExecuteSql(strDetailSQL);
                result = "执行成功";


            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;
        }

        #region 同时插入主表和明细表数据(new)
        /// <summary>
        /// 同时插入主表和明细表数据
        /// </summary>
        /// <param name="mainTable">主表的表名</param>
        /// <param name="mainList">往主表中将要插入的数据 [key,value,type]</param>
        /// <param name="detailTable">从表的表名</param>
        /// <param name="detailList">往从表中插入的数据 []</param>
        /// <returns></returns>
        public string InsertMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string detailTable, List<SortedDictionary<string, string>> detailList)
        {

            //第一步插入主表数据
            string result = "";
            try
            {
                string strMainSQL = GetInsertSql(mainTable, mainList);
                //组合从表的SQL语句####################################
                string strDetailSQL = "";
                int count = detailList.Count;
                for (int j = 0; j < count; j++)
                {
                    strDetailSQL += GetInsertSql(detailTable, detailList[j]);
                }

                try
                {
                   if( SQLHelper.ExecuteSql(strMainSQL + "" + strDetailSQL))
                    result = "执行成功";
                   else
                       result = "执行失败";
                }
                catch (Exception ex)
                {
                    result = ex.Message.ToString();
                }
            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;

        }

        #endregion


        #region 同时更新主表和明细表
        public string UpdateMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string[] primaryKey, string detailTable, List<SortedDictionary<string, string>> detailList)
        {

            //1:组合更新主表的SQL语句
            mainList.Add(primaryKey[0], primaryKey[1]);//更新条件
            string result = "";
            string strMainSQL = GetUpdateSql(mainTable, mainList);
            //2:组合子表的SQL语句
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                strDetailSQL += GetInsertSql(detailTable, detailList[j]);
            }
            //3:组合删除从表SQL语句-----先删除旧数据，然后插入新数据
            string strOldSQL = "delete " + detailTable + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "N" || primaryKey[2] == "D")
                strOldSQL += primaryKey[1];
            else
                strOldSQL += "'" + primaryKey[1] + "'";

            //4：执行SQL语句（带有事务）
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL + " " + strOldSQL + ";" + strDetailSQL))
                    result = "执行成功";
                else
                    result = "执行失败";

            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
        #endregion

        #region 同时插入主表和明细表数据
        /// <summary>
        /// 同时插入主表和明细表数据
        /// </summary>
        /// <param name="mainTable">主表的表名</param>
        /// <param name="mainList">往主表中将要插入的数据 [key,value,type]</param>
        /// <param name="detailTable">从表的表名</param>
        /// <param name="fCode">从表中主表的主键名称（从表的外键）</param>
        /// <param name="detailList">往从表中插入的数据 []</param>
        /// <returns></returns>
        public string InsertMainAndDetail(string mainTable, List<String[]> mainList, string detailTable, string fCode, List<String[]> detailList)
        {

            //第一步插入主表数据

            string key = "";
            string value = "";
            int len = mainList.Count;

            for (int i = 0; i < len; i++)
            {
                key += mainList[i][0].ToString();//键
                ////N是整数 D是decimal
                if (mainList[i][2].ToString() == "N")//类型
                    value += Int32.Parse(mainList[i][1].ToString());//值
                else if (mainList[i][2].ToString() == "D")
                    value += Decimal.Parse(mainList[i][1].ToString());//值
                else if (mainList[i][2].ToString() == "A")//date 的第二个字母
                    value += "'" + DateTime.Parse(mainList[i][1].ToString()) + "'";//值
                else
                    value += "'" + RegexRule.HTMLEncode(mainList[i][1].Trim()) + "'";
                if (i < (len - 1))
                {
                    key += ",";
                    value += ",";
                }
            }
            string strMainSQL = "INSERT INTO " + mainTable + " (" + key + ") VALUES (" + value + ")";


            //从表
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int i = 0; i < count; i++)//逐条插入数据
            {
                strDetailSQL += "Insert Into " + detailTable + " (" + fCode + ",ProductType,Num,Price,Unit,Profile) Values ('" + detailList[i][0] + "','" + RegexRule.HTMLEncode(detailList[i][1]) + "'," + Int32.Parse(detailList[i][2].Trim()) + "," + decimal.Parse(detailList[i][3].Trim()) + ",'" + RegexRule.HTMLEncode(detailList[i][4]) + "','" + RegexRule.HTMLEncode(detailList[i][5]) + "')";
                if (i < (count - 1))
                    strDetailSQL += ";";
            }

            string result = "";
            try
            {
                SQLHelper.ExecuteSql(strMainSQL + ";" + strDetailSQL);
                result = "执行成功";

            }
            catch //(Exception ex)
            {
                result = "添加数据错误";
            }

            return result;

        }

        #endregion





    }
}